/*
 Navicat Premium Dump SQL

 Source Server         : 120.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 80021 (8.0.21)
 Source Host           : localhost:3306
 Source Schema         : iot

 Target Server Type    : MySQL
 Target Server Version : 80021 (8.0.21)
 File Encoding         : 65001

 Date: 28/05/2025 14:37:46
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tsl_device
-- ----------------------------
DROP TABLE IF EXISTS `tsl_device`;
CREATE TABLE `tsl_device` (
  `id` varchar(64) NOT NULL COMMENT '设备主键，UUID',
  `name` varchar(128) DEFAULT NULL COMMENT '设备名称',
  `product_key` varchar(64) NOT NULL COMMENT '产品唯一标识 Key',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备主表';

-- ----------------------------
-- Records of tsl_device
-- ----------------------------
BEGIN;
INSERT INTO `tsl_device` (`id`, `name`, `product_key`, `create_time`, `update_time`) VALUES ('device123', '测试设备', 'abc123XYZ', '2025-05-28 14:30:41', '2025-05-28 14:30:49');
COMMIT;

-- ----------------------------
-- Table structure for tsl_device_spec
-- ----------------------------
DROP TABLE IF EXISTS `tsl_device_spec`;
CREATE TABLE `tsl_device_spec` (
  `device_id` varchar(64) NOT NULL COMMENT '设备 ID',
  `spec_key` varchar(64) NOT NULL COMMENT '扩展字段键，如 protocol, modbus/host 等',
  `spec_value` json DEFAULT NULL COMMENT '扩展字段值，JSON 格式',
  PRIMARY KEY (`device_id`,`spec_key`),
  KEY `idx_tsl_device_spec_device` (`device_id`),
  CONSTRAINT `fk_tsl_device_spec_device` FOREIGN KEY (`device_id`) REFERENCES `tsl_device` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备协议/模块扩展信息表';

-- ----------------------------
-- Records of tsl_device_spec
-- ----------------------------
BEGIN;
INSERT INTO `tsl_device_spec` (`device_id`, `spec_key`, `spec_value`) VALUES ('device123', 'modbusConfig', '{\"host\": \"192.168.1.100\", \"port\": 502, \"unitId\": 1}');
INSERT INTO `tsl_device_spec` (`device_id`, `spec_key`, `spec_value`) VALUES ('device123', 'protocol', '\"modbus\"');
COMMIT;

-- ----------------------------
-- Table structure for tsl_event
-- ----------------------------
DROP TABLE IF EXISTS `tsl_event`;
CREATE TABLE `tsl_event` (
  `event_id` varchar(64) NOT NULL COMMENT '事件主键，UUID',
  `device_id` varchar(64) NOT NULL COMMENT '所属设备 ID',
  `name` varchar(128) NOT NULL COMMENT '事件名称',
  `description` varchar(255) DEFAULT NULL COMMENT '事件描述',
  `type` varchar(16) NOT NULL DEFAULT 'info' COMMENT '事件类型: info, alert, error',
  `required` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否必选（0=false,1=true）',
  `output_data` json DEFAULT NULL COMMENT '事件输出参数列表，存 JSON 数组',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`event_id`),
  KEY `idx_tsl_event_device` (`device_id`),
  CONSTRAINT `fk_tsl_event_device` FOREIGN KEY (`device_id`) REFERENCES `tsl_device` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备事件固定字段表（含 type, required, output_data）';

-- ----------------------------
-- Records of tsl_event
-- ----------------------------
BEGIN;
INSERT INTO `tsl_event` (`event_id`, `device_id`, `name`, `description`, `type`, `required`, `output_data`, `create_time`, `update_time`) VALUES ('f47ac10b-58cc-4372-a567-0e02b2c3d479', 'device123', 'overheatAlarm', '设备温度超限报警', 'alert', 0, '[{\"pid\": \"value\", \"name\": \"当前温度\", \"dataType\": {\"type\": \"float\", \"specs\": {\"max\": 125, \"min\": -40, \"unit\": \"℃\"}}, \"customFields\": {}}]', '2025-05-28 09:42:18', '2025-05-28 09:42:18');
COMMIT;

-- ----------------------------
-- Table structure for tsl_event_attr
-- ----------------------------
DROP TABLE IF EXISTS `tsl_event_attr`;
CREATE TABLE `tsl_event_attr` (
  `event_id` varchar(64) NOT NULL COMMENT '事件主键，关联 tsl_event.event_id',
  `attr_key` varchar(64) NOT NULL COMMENT '扩展字段键',
  `attr_value` varchar(255) DEFAULT NULL COMMENT '扩展字段值',
  PRIMARY KEY (`event_id`,`attr_key`),
  KEY `idx_tsl_event_attr_event` (`event_id`),
  CONSTRAINT `fk_tsl_event_attr_event` FOREIGN KEY (`event_id`) REFERENCES `tsl_event` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备事件动态扩展字段表';

-- ----------------------------
-- Records of tsl_event_attr
-- ----------------------------
BEGIN;
INSERT INTO `tsl_event_attr` (`event_id`, `attr_key`, `attr_value`) VALUES ('f47ac10b-58cc-4372-a567-0e02b2c3d479', 'level', 'high');
INSERT INTO `tsl_event_attr` (`event_id`, `attr_key`, `attr_value`) VALUES ('f47ac10b-58cc-4372-a567-0e02b2c3d479', 'threshold', '75');
COMMIT;

-- ----------------------------
-- Table structure for tsl_product
-- ----------------------------
DROP TABLE IF EXISTS `tsl_product`;
CREATE TABLE `tsl_product` (
  `product_key` varchar(64) NOT NULL COMMENT '产品唯一标识 Key',
  `version` varchar(32) NOT NULL COMMENT '物模型版本号',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`product_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='TSL 产品 Profile 表';

-- ----------------------------
-- Records of tsl_product
-- ----------------------------
BEGIN;
INSERT INTO `tsl_product` (`product_key`, `version`, `create_time`, `update_time`) VALUES ('abc123XYZ', '1.0.0', '2025-05-28 09:45:43', '2025-05-28 09:45:43');
COMMIT;

-- ----------------------------
-- Table structure for tsl_property
-- ----------------------------
DROP TABLE IF EXISTS `tsl_property`;
CREATE TABLE `tsl_property` (
  `property_id` varchar(64) NOT NULL COMMENT '属性主键，UUID',
  `device_id` varchar(64) NOT NULL COMMENT '所属设备 ID',
  `property_name` varchar(128) NOT NULL COMMENT '属性名称',
  `data_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '数据类型，如 int、float、text、bool',
  `access_mode` varchar(10) NOT NULL COMMENT 'r:只读, rw:读写',
  `measure_unit` varchar(16) DEFAULT NULL COMMENT '单位，如 C, %',
  `default_value` varchar(64) DEFAULT NULL COMMENT '默认值',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`property_id`),
  KEY `idx_device_id` (`device_id`),
  CONSTRAINT `fk_tsl_property_device` FOREIGN KEY (`device_id`) REFERENCES `tsl_device` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备属性固定字段表';

-- ----------------------------
-- Records of tsl_property
-- ----------------------------
BEGIN;
INSERT INTO `tsl_property` (`property_id`, `device_id`, `property_name`, `data_type`, `access_mode`, `measure_unit`, `default_value`, `create_time`, `update_time`) VALUES ('550e8400-e29b-41d4-a716-446655440002', 'device123', 'temperature', 'float', 'rw', 'C', '25.0', '2025-05-28 11:04:55', '2025-05-28 11:04:55');
COMMIT;

-- ----------------------------
-- Table structure for tsl_property_attr
-- ----------------------------
DROP TABLE IF EXISTS `tsl_property_attr`;
CREATE TABLE `tsl_property_attr` (
  `property_id` varchar(64) NOT NULL COMMENT '属性主键，关联 tsl_property.property_id',
  `attr_key` varchar(64) NOT NULL COMMENT '扩展字段键',
  `attr_value` varchar(255) DEFAULT NULL COMMENT '扩展字段值（统一用字符串存储）',
  PRIMARY KEY (`property_id`,`attr_key`),
  KEY `idx_prop_id` (`property_id`),
  CONSTRAINT `fk_tsl_prop_attr_prop` FOREIGN KEY (`property_id`) REFERENCES `tsl_property` (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备属性动态扩展字段表';

-- ----------------------------
-- Records of tsl_property_attr
-- ----------------------------
BEGIN;
INSERT INTO `tsl_property_attr` (`property_id`, `attr_key`, `attr_value`) VALUES ('550e8400-e29b-41d4-a716-446655440002', 'alarm', 'true');
INSERT INTO `tsl_property_attr` (`property_id`, `attr_key`, `attr_value`) VALUES ('550e8400-e29b-41d4-a716-446655440002', 'threshold', '30');
COMMIT;

-- ----------------------------
-- Table structure for tsl_service
-- ----------------------------
DROP TABLE IF EXISTS `tsl_service`;
CREATE TABLE `tsl_service` (
  `service_id` varchar(64) NOT NULL COMMENT '服务主键，UUID',
  `device_id` varchar(64) NOT NULL COMMENT '所属设备 ID',
  `name` varchar(128) NOT NULL COMMENT '服务名称',
  `description` varchar(255) DEFAULT NULL COMMENT '服务描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `required` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否必选（0=false,1=true）',
  `call_type` varchar(16) NOT NULL DEFAULT 'sync' COMMENT '调用类型: async|sync',
  `input_data` json DEFAULT NULL COMMENT '命令入参列表(JSON 数组)',
  `output_data` json DEFAULT NULL COMMENT '命令出参列表(JSON 数组)',
  `method` varchar(128) NOT NULL COMMENT '命令对应方法名',
  PRIMARY KEY (`service_id`),
  KEY `idx_tsl_service_device` (`device_id`),
  CONSTRAINT `fk_tsl_service_device` FOREIGN KEY (`device_id`) REFERENCES `tsl_device` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备服务固定字段表';

-- ----------------------------
-- Records of tsl_service
-- ----------------------------
BEGIN;
INSERT INTO `tsl_service` (`service_id`, `device_id`, `name`, `description`, `create_time`, `update_time`, `required`, `call_type`, `input_data`, `output_data`, `method`) VALUES ('rebootDevice', 'device123', 'rebootDevice', '设备远程重启', '2025-05-28 09:21:52', '2025-05-28 09:21:52', 1, 'async', '[{\"pid\": \"delay\", \"name\": \"延迟时间\", \"dataType\": {\"type\": \"int\", \"specs\": {\"max\": 300, \"min\": 0, \"unit\": \"s\"}}, \"customFields\": {}}]', '[{\"pid\": \"status\", \"name\": \"执行状态\", \"dataType\": {\"type\": \"text\"}, \"customFields\": {}}]', 'invokeRebootDevice');
COMMIT;

-- ----------------------------
-- Table structure for tsl_service_attr
-- ----------------------------
DROP TABLE IF EXISTS `tsl_service_attr`;
CREATE TABLE `tsl_service_attr` (
  `service_id` varchar(64) NOT NULL COMMENT '服务主键，关联 tsl_service.service_id',
  `attr_key` varchar(64) NOT NULL COMMENT '扩展字段键',
  `attr_value` varchar(255) DEFAULT NULL COMMENT '扩展字段值',
  PRIMARY KEY (`service_id`,`attr_key`),
  KEY `idx_tsl_service_attr_service` (`service_id`),
  CONSTRAINT `fk_tsl_service_attr_service` FOREIGN KEY (`service_id`) REFERENCES `tsl_service` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备服务动态扩展字段表';

-- ----------------------------
-- Records of tsl_service_attr
-- ----------------------------
BEGIN;
INSERT INTO `tsl_service_attr` (`service_id`, `attr_key`, `attr_value`) VALUES ('rebootDevice', 'retry', '3');
INSERT INTO `tsl_service_attr` (`service_id`, `attr_key`, `attr_value`) VALUES ('rebootDevice', 'timeout', '60');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
